import mssql
import config

# 查询命令
def query(sql):
    db = mssql.ZgqMssql(config.get('db'))
    dataList = db.query(sql)
    db.close()
    return dataList

# 获取挂号列表
def getRegisterList(doct_id:int, sect_id:int):
    sql = 'SELECT' + \
    ' register.pres_id,' + \
    ' register.seq,' + \
    ' patient.patient_id,' + \
    ' patient.card_nbr patient_card_nbr,' + \
    ' convert(nvarchar(1000),patient.name) patient_name,' + \
    ' convert(nvarchar(1000),patient.addr) patient_addr,' + \
    ' patient.contact_detail patient_contact_tel,' + \
    ' patient.age patient_age,' + \
    ' convert(nvarchar(1000),case when patient.sex = 1 THEN \'男\' ELSE \'女\' END) patient_sex,' + \
    ' register.sts' + \
    ' FROM register INNER JOIN  patient ON register.patient_id = patient.patient_id' + \
    ' WHERE convert(VARCHAR(10),register_date, 120) = convert(VARCHAR(10),GETDATE(),120)' + \
    ' and register.doct_id IN (0, ' + str(doct_id) + ')' + \
    ' and register.sts in (\'A\',\'C\')' + \
    ' AND register.sect_id IN (SELECT SECT_ID FROM sect_staff WHERE DOCT_ID IN (' + str(doct_id) + '))' + \
    ' order by register.seq'
    print(sql)
    list = query(sql)
    return {
        "code": 200,
        "data": {
            "dataList": list
        }
    }
    
def setRegister(data):
    # 开始保存数据
    db = mssql.ZgqMssql(config.get('db'))
    # 更新挂号状态
    sql = 'update register set sts = \'' + data['sts'] +'\' where pres_id = \'' + data['pres_id'] + '\''
    rowcount = db.execute(sql)
    db.commit();
    db.close()
    return {
        "code": 200,
        "msg": "success"
    }
    
